﻿using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Data.ConnectionUI;
using System.Xml;
using System.IO;
using System.Text;

namespace SQL2Entity
{
    public partial class frmMain : Form
    {

        private XmlDocument templatexml;
        private EntityTemplates template;
        SqlConnection sql;
        /// <summary>
        /// 
        /// </summary>
        public frmMain()
        {
            InitializeComponent();
        }
        /// <summary>
        /// 
        /// </summary>
        private static String[,] DBTypeConversionKey = new String[,] 
            {
             {"Binary", "System.Byte[]"},
             {"Byte", "System.Byte"},
             {"Boolean", "System.Boolean"},
             {"DateTime", "System.DateTime"},
             {"Decimal", "System.Decimal"},
             {"Double", "System.Double"},
             {"Guid", "System.Guid"},
             {"Int16", "System.Int16"},
             {"Int32", "System.Int32"},
             {"Int64", "System.Int64"},
             {"Object", "System.Object"},
             {"SByte", "System.SByte"},
             {"Single", "System.Single"},
             {"String", "System.String"},
             {"UInt16", "System.UInt16"},
             {"UInt32", "System.UInt32"},
             {"UInt64", "System.UInt64"},
             {"Xml", "System.Xml"},
             {"Varchar","System.String"},
             {"Uniqueidentifier","System.Guid"},
             {"Nchar","System.String"},
             {"Nvarchar","System.String"}
            };
        /// <summary>
        /// 
        /// </summary>
        /// <param name="content"></param>
        /// <param name="dir"></param>
        /// <param name="filename"></param>
        private void doSaveEntityFile(string content, string dir, string filename)
        {
            FileStream fs = new FileStream(dir+'\\'+ filename, FileMode.Create);
            byte[] data = new UTF8Encoding().GetBytes(content);
            fs.Write(data, 0, data.Length);
            fs.Flush();
            fs.Close();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="msg"></param>
        private void doOutputLog(string msg)
        {
            rtbEnContent.Text = String.Format("{0}\n{1}", rtbEnContent.Text, msg);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dir"></param>
        private void doGenerateEntity(String tableName, string dir)
        {
            string entitycontent;
            EntityTemplate classtemplate = template.Templates.Find(delegate(EntityTemplate a) { return a.TemplateName == "EntityClass"; });

            if (classtemplate == null)
            {
                throw new Exception("xml format error");
            }

            EntityItem importitem = classtemplate.Items.Find(delegate(EntityItem a) { return a.Section == "Importing"; });
            if (importitem == null)
            {
                throw new Exception("xml format error");
            }
            entitycontent = importitem.Code;

            EntityItem npitem = classtemplate.Items.Find(delegate(EntityItem a) { return a.Section == "Namespace"; });

            if (npitem == null)
            {
                throw new Exception("xml format error");
            }
            entitycontent = entitycontent +'\n'+ String.Format(npitem.Code, tbNamespace.Text);

            EntityItem classitem = classtemplate.Items.Find(delegate(EntityItem a) { return a.Section == "ClassName"; });
            if (classitem == null)
            {
                throw new Exception("xml format error");
            }

            entitycontent = entitycontent  + "{" + '\n' + string.Format(classitem.Code, tableName);

            EntityItem fielditem = classtemplate.Items.Find(delegate(EntityItem a) { return a.Section == "Fields"; });
            if (classitem == null)
            {
                throw new Exception("xml format error");
            }
            EntityItem propertyItem = classtemplate.Items.Find(delegate(EntityItem a) { return a.Section == "Properties"; });
            if (classitem == null)
            {
                throw new Exception("xml format error");
            }
            string fieldcontent=String.Empty;
            string propertycontent=String.Empty;
            if (sql.State == ConnectionState.Closed)
            {
                sql.Open();
            }
            using (SqlCommand command = new SqlCommand(String.Format("select * from {0}", tableName), sql))
            {
                
                DataTable table = command.ExecuteReader().GetSchemaTable();
                
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    if ((fieldcontent==null)&&(fieldcontent.Equals(String.Empty)))
                    {
                        fieldcontent = string.Format(fielditem.Code, this.ConvertToType(table.Rows[i]["DataTypeName"].ToString()), "_"+table.Rows[i]["ColumnName"].ToString());
                    }
                    else
                    {
                        fieldcontent = fieldcontent + '\n'+string.Format(fielditem.Code, this.ConvertToType(table.Rows[i]["DataTypeName"].ToString()), "_" + table.Rows[i]["ColumnName"].ToString());
                    }
                    if ((propertycontent==null)&&(propertycontent.Equals(String.Empty)))
                    {
                        propertycontent = string.Format(propertyItem.Code, this.ConvertToType(table.Rows[i]["DataTypeName"].ToString()), table.Rows[i]["ColumnName"].ToString(), "_" + table.Rows[i]["ColumnName"].ToString(), "_" + table.Rows[i]["ColumnName"].ToString());
                    }else
                    {
                        propertycontent = propertycontent + '\n' + string.Format(propertyItem.Code, this.ConvertToType(table.Rows[i]["DataTypeName"].ToString()), table.Rows[i]["ColumnName"].ToString(), "_" + table.Rows[i]["ColumnName"].ToString(), "_" + table.Rows[i]["ColumnName"].ToString());
                    }
                }

                command.Dispose();
                sql.Close();
            }

            entitycontent = entitycontent + "{"+fieldcontent + propertycontent.Replace('(','{').Replace(')','}')+"}\n"+"}";



            doSaveEntityFile(entitycontent,dir, tableName + ".cs");

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbtype"></param>
        /// <returns></returns>
        private String ConvertToType(String dbtype)
        {
            String TypeName = String.Empty;

            int keyCount = DBTypeConversionKey.GetLength(0);
            for (int i = 0; i < keyCount; i++)
            {
                if (DBTypeConversionKey[i, 0].Equals(dbtype))
                {
                    TypeName = DBTypeConversionKey[i, 1];
                    break;
                }

            }

            if (TypeName == String.Empty)
            {
                TypeName = "System.String";
            }
            //dbTypeName = "Variant";
            return TypeName;

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnConnect_Click(object sender, EventArgs e)
        {
            rtbEnContent.Clear();
            lbTables.Items.Clear();
            if (!tbFileName.Text.Equals(""))
            {
                try
                {
                    if (sql == null)
                    {
                        sql = new SqlConnection(tbFileName.Text);
                    }

                    if (sql.State == ConnectionState.Closed)
                    {
                        sql.Open();
                    }
                    doOutputLog("connected to Database.");
                    DataTable schema = sql.GetSchema("Tables");
                    for (int i = 0; i < schema.Rows.Count; i++)
                    {
                        lbTables.Items.Add(schema.Rows[i]["TABLE_NAME"]);
                    }
                    doOutputLog("get " + schema.Rows.Count + " Tables.");
                }
                catch (Exception er)
                {
                    doOutputLog(er.Message);
                }

            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnGenerate_Click(object sender, EventArgs e)
        {
            if (tbNamespace.Text.Equals(""))
            {
                MessageBox.Show("please input namespace!");
                tbNamespace.Focus();
                return;
            }
            doOutputLog("start generate entity files");
            if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                for (int i = 0; i < lbTables.SelectedItems.Count; i++)
                {
                    doOutputLog("process talbe " + lbTables.SelectedItems[i]);
                    doGenerateEntity(lbTables.SelectedItems[i].ToString(), folderBrowserDialog1.SelectedPath);
                }
            }
            doOutputLog("finished generate entity files");
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnConnStr_Click(object sender, EventArgs e)
        {
            DataConnectionDialog dialog = new DataConnectionDialog();
            DataSource.AddStandardDataSources(dialog);
            dialog.SelectedDataSource = DataSource.SqlDataSource;
            dialog.SelectedDataProvider = DataProvider.SqlDataProvider;

            if (DataConnectionDialog.Show(dialog) == DialogResult.OK)
            {
                tbFileName.Text = dialog.ConnectionString;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void frmMain_Load(object sender, EventArgs e)
        {
            if (templatexml == null)
            {
                templatexml = new XmlDocument();
                templatexml.Load(@".\template.xml");
                if (template == null)
                {
                    template = SerializeUtils.Deserialize<EntityTemplates>(templatexml.OuterXml);
                }
                
            }
        }
    }
}
